package com.zyh.day04.sql

import org.apache.spark.sql.{DataFrame, SparkSession}

object CaseWhenTest {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .appName("where")
      .master("local[*]")
      .getOrCreate()
    //RDD[case class] --> DataFrame
    //Scala集合[case class] --> DataFrame
    val list = List((1, "xiaohei", 18, "1"), (2, "xiaobai", 20, "2"))

    import spark.implicits._
    val scoreDF = List((1, "语文", 100), (1, "数学", 100), (1, "英语", 100),
      (2, "数学", 79), (2, "语文", 80), (2, "英语", 100))
      .toDF("id", "course", "score")

    scoreDF.createOrReplaceTempView("t_course")

    val sql =
      """
        select id ,
               max(case course when '语文' then score else 0 end ) chinese,
               max(case course when '数学' then score else 0 end ) math,
               max(case course when '英语' then score else 0 end ) english
        from t_course
        group by id
      """

    spark.sql(sql).show()
    spark.close()
  }
}
